﻿//生成[MaterialGoods]Repository代码
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FineAdmin.IRepository;
using FineAdmin.Model;
using Dapper;
using System.Data.SqlClient;

namespace FineAdmin.Repository
{
    public class MaterialGoodsRepository : BaseRepository<MaterialGoodsModel>, IMaterialGoodsRepository
    {
        public IEnumerable<MaterialGoodsModel> GetMaterialGoodsByItemID(string itemId)
        {
            using (var conn = SqlHelper.SqlConnection())
            {

                string sql = @"SELECT  * FROM MaterialGoods where ItemId='" + itemId + "'";
                return conn.Query<MaterialGoodsModel>(sql);
            }
        }

        public IEnumerable<MaterialGoodsModel> GetMaterialGoodsList()
        {
            using (var conn = SqlHelper.SqlConnection())
            {
                string sql = @"SELECT  * FROM MaterialGoods ORDER BY SortCode ASC";
                return conn.Query<MaterialGoodsModel>(sql);
            }
        }

        public IEnumerable<MaterialGoodsModel> GetMaterialGoodsDistinct(PageInfo pageInfo, out int total, string where = null)
        {

            //StringBuilder sb = new StringBuilder();

            //sb.AppendFormat("SELECT COUNT(1) FROM {0};", where);

            //sb.AppendFormat("select top {3} * from(select row_number() over(order by {4}CreateTime desc) as rownumber,{0} from {1}) temp_table where rownumber > (({2}-1)*{3})", returnFields, where, pageIndex, pageSize, prefix);
            //using (var reader = conn.QueryMultiple(sb.ToString(), param, transaction, commandTimeout))
            //{
            //    total = reader.ReadFirst<int>();
            //    return reader.Read<T>();
            //}
            int skip = 0;
            if (pageInfo.page > 0)
            {
                skip = (pageInfo.page - 1) * pageInfo.limit;
            }
            string order = string.Empty;
            order = string.Format("order by {0} {1}", pageInfo.field,pageInfo.order);
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT COUNT(1) FROM MaterialGoods {0} AND id in(select MAX(id) from MaterialGoods group by ItemId );", where);
            sb.AppendFormat("WITH cte AS(select ROW_NUMBER() OVER({0}) as rownum,* from MaterialGoods {1} and id in(select MAX(id) from MaterialGoods group by ItemId) ) select MaterialGoods.* from MaterialGoods INNER JOIN cte on cte.Id = MaterialGoods.Id where rownum BETWEEN {2} AND {3}", order, where, skip + 1, skip+pageInfo.limit);
            using (var conn = SqlHelper.SqlConnection())
            {
                var reader = conn.QueryMultiple(sb.ToString(), null, null, null);

                total = reader.ReadFirst<int>();
                return reader.Read<MaterialGoodsModel>();
            }
        }
    }
}